import numpy as np
import pandas as pd
import pymysql
from sqlalchemy import create_engine


def main():
    # 通过二维数组创建DataFrame对象
    scores = np.random.randint(60, 101, (5, 3))
    courses = ['语文', '数学', '英语']
    stu_ids = np.arange(1001, 1006)
    df1 = pd.DataFrame(data=scores, columns=courses, index=stu_ids)
    print(f'df1: {df1}')


def main2():
    # 通过字典创建DataFrame对象
    scores = {
        '语文': [62, 72, 93, 88, 93],
        '数学': [95, 65, 86, 66, 87],
        '英语': [66, 75, 82, 69, 82],
    }
    stu_ids = np.arange(1001, 1006)
    df2 = pd.DataFrame(data=scores, index=stu_ids)
    print(f'df2: {df2}')


def main3():
    # 创建一个MySQL数据库的连接对象
    conn = pymysql.connect(
        host='127.0.0.1', port=3306,
        user='guest', password='Guest.618',
        database='study', charset='utf8mb4'
    )
    # 通过SQL从数据库二维表读取数据创建DataFrame
    df5 = pd.read_sql('select * from tb_dept', conn, index_col='no')
    print(f'df5: {df5}')


def main4():
    # 通过指定的URL（统一资源定位符）访问数据库
    engine = create_engine('mysql+pymysql://guest:Guest.618@127.0.0.1:3306/study')
    # 直接通过表名加载整张表的数据
    df5 = pd.read_sql('tb_dept', engine, index_col='no')
    print(f'df5: {df5}')
    df6 = pd.read_sql('select no, name, location from tb_dept', engine, index_col='no')
    print(f'df6: {df6}')
    print(df6.info())
    print(df6.columns)
    # print(df6.name[1])
    # print(df6[(df6.no == 0) & (df6.name == '财务部')])
    # filtered_df = df6[(df6['no'] == 0) & (df6['name'] == '财务部')]
    # print(f'filtered_df: {filtered_df}')
    engine.connect().close()


def main5():
    data = {
        'no': [1, 2, 3, 4, 5],
        'name': ['财务部', '技术部', '销售部', '财务部', '市场部'],
        'location': ['上海', '北京', '深圳', '上海', '北京'],
        'value': [10, 20, 30, 40, 50]
    }
    df6 = pd.DataFrame(data)
    # 打印 DataFrame
    print(df6)
    # 检查列名
    print(df6.columns)
    # 过滤 DataFrame 中 no 等于 0 且 name 等于 '财务部' 的行
    filtered_df = df6[(df6.no == 1) & (df6['name'] == '财务部')]
    # 打印过滤后的 DataFrame
    print(filtered_df)
    filtered_df6 = df6.query('no > 1 and value == 20')
    print(f'filtered_df6: {filtered_df6}')


if __name__ == '__main__':
    main5()
